﻿// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data.Common;

using System.Data;
using System.Data.OleDb;

namespace net.windward.utils.ado.SPList
{
	/// <summary>
	/// An SharePoint List database.
	/// </summary>
	public class WrSPListDatabase : IWrDatabase
	{
        private readonly DbProviderFactory provider;
        private readonly string server;
        private readonly string database;
        private static readonly Hashtable types;
        private readonly IWrSyntax syntax;
		private WrTable[] tables;

        /// <summary>
        /// Name of the provider used (in this case OleDb; called odbcProvider for consistancy across classes)
        /// </summary>
        protected readonly string odbcProvider;

        static WrSPListDatabase()
		{
            types = new Hashtable();
            types.Add("System.Double", DbType.Double);
            types.Add("System.String", DbType.String);
            types.Add("System.DateTime", DbType.DateTime);
            types.Add("System.Int32", DbType.Int32);
        }

	    /// <summary>
	    /// Create an SharePoint List database object.
	    /// </summary>
	    /// <param name="provider">The provider for this database.</param>
	    /// <param name="odbcProvider">The name of the data provider used.</param>
	    /// <param name="server">The name of the server</param>
	    /// <param name="database">The name of the database</param>
	    public WrSPListDatabase(DbProviderFactory provider, string odbcProvider, string server, string database)
		{
            this.provider = provider;
            this.odbcProvider = odbcProvider;
            this.server = server;
            this.database = database;
			syntax = new WrSPListSyntax(provider, odbcProvider);
		}

        /// <summary>
        /// The name of the database.
        /// </summary>
        public string Name
        {
            get { return database; }
        }

        /// <summary>
        /// The default schema for all objects in this database. Returns null if there is no default.
        /// </summary>
        public string DefaultSchema
        {
            get
            {
                return null;
            }
        }

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return null; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return null; }
		}


		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(ConnectionString("INFORMATION_SCHEMA", credentials), owner,status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			tables = GetTables(connStr, owner);
		}

        /// <summary>
        /// Return all tables in this database.
        /// </summary>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
        /// <returns>all tables in this database.</returns>
        private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner)
        {

            if ((owner & WrVendor.ELEM_OWNER.USER) == 0)
                return new WrTable[0];

        	WrColumn[] columns = GetColumns(connStr);
        	return new WrTable[] {new WrTable(null, "list", "", false, columns)};
        }
  
        /// <summary>
        /// Return all columns in a table or view.
        /// </summary>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <returns>all columns in a table or view.</returns>
		private static WrColumn[] GetColumns(string connStr)
        {
            // NEEDS TO BE UPDATED
            try
            {
                ArrayList rtn = new ArrayList();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    DataSet myDataSet = new DataSet();
                    OleDbCommand myAccessCommand = new OleDbCommand("SELECT * FROM list", conn);
                    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

                    conn.Open();

                    myDataAdapter.Fill(myDataSet, "list");
                    DataColumnCollection drc = myDataSet.Tables["list"].Columns;

                    foreach (DataColumn dc in drc)
                    {
                        string str = dc.DataType.ToString();
                        DbType typ = (DbType)(types.Contains(str) ? types[str] : DbType.Object);
                        //Console.WriteLine("Column name[{0}] is {1}, of type {2}", i++, dc.ColumnName, dc.DataType);
                        rtn.Add(new WrColumn((string)dc.ColumnName, "", typ));
                    }
                }
                return (WrColumn[])rtn.ToArray(typeof(WrColumn));
            }
            catch (Exception)
            {
                return new WrColumn[0];
            }
        }

        private string ConnectionString(string dbName, WrCredentials credentials)
        {
            return WrSPListVendor.ConnectionString(provider, database, server, credentials, true);
        }
	}
}
